package com.cari.oa.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.cari.oa.domain.SoftProjectEntity;
import com.cari.oa.domain.SoftProjectUserRelEntity;
import com.cari.oa.util.StrUtils;

/**
 * 
* Title: SoftProjectDao
* Description:软件项目Dao
* Company: cari 
* @author    hjx 
* @date       2013-1-16
 */
@Repository
public class SoftProjectDao extends BaseDao<SoftProjectEntity> {
	
    /**
    * Title: getPage
    * Description:分页查询软件项目实体对象
    * @author    hjx 
    * @date       2013-1-16 
    * @param name 项目名称
    * @param status 项目状态
    * @param startTime 开始时间
    * @param page 第几页
    * @param size 每页显示几条
    * @return
     */
    public List<SoftProjectEntity> getPage(String name, String status, Date startTime,Date endTime,int page, int size) {
    	
    	List<Object> params = new ArrayList<Object>();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("from SoftProjectEntity sp where 1=1 ");

        if(StrUtils.isNotEmpty(name)) {
            stringBuilder.append("and sp.name like ? ");
            params.add("%" + name + "%");
        }

        if(StrUtils.isNotEmpty(status)) {
            stringBuilder.append("and sp.status like ? ");
            params.add("%" + status + "%");
        }
        
        if(startTime!=null) {
            stringBuilder.append("and sp.createTime >= ? ");
            params.add(startTime);
        }
        
        if(endTime != null) {
            stringBuilder.append("and sp.createTime <= ? ");
            params.add(endTime);
        }
        stringBuilder.append(" order by  createTime desc");
        return getPage(stringBuilder.toString(), page, size, params.toArray());
    }
    
   
    /**
    * Title: getCount
    * Description: 查询符合条件的记录数
    * @author    hjx 
    * @date       2013-1-16 
    * @param name 项目名称
    * @param status 项目状态
    * @param startTime 开始时间
    * @return
    */
    public long getCount(String name, String status, Date startTime, Date endTime) {
    	
    	List<Object> params = new ArrayList<Object>();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select count(*) from SoftProjectEntity sp where 1=1 ");

        if(StrUtils.isNotEmpty(name)) {
            stringBuilder.append("and sp.name like ? ");
            params.add("%" + name + "%");
        }

        if(StrUtils.isNotEmpty(status)) {
            stringBuilder.append("and sp.status like ? ");
            params.add("%" + status + "%");
        }
        
        if(startTime!=null) {
            stringBuilder.append("and sp.createTime >= ? ");
            params.add(startTime);
        }
        
        if(endTime != null) {
            stringBuilder.append("and sp.createTime <= ? ");
            params.add(endTime);
        }
        Query query = getSession().createQuery(stringBuilder.toString());
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i, params.get(i));
        }
        return (Long)query.uniqueResult();
    }
    
    /**
     * 判断该项目名称是否存在
     * @param name 项目的名称
     * @return 是否存在
     */
    public boolean existByName(String name) {
        Query query = getSession().createQuery("select count(*) from SoftProjectEntity where name = :name");
        query.setString("name", name);
        Long count = (Long)query.uniqueResult();
        return count > 0;
    }

    /**
	* Title: addProjectUserRel
	* Description: 添加参与项目的用户关系
	* @author    hjx 
	* @date      2013-1-17 
	* @param projectId
	* @param selectedUserEnname
	*/
	public void addProjectUserRel(String projectId,List<String> selectedUserEnname,String type) {
		if(StrUtils.isEmpty(projectId));
		     new RuntimeException("项目Id不能为空");
	    StringBuilder hql = new StringBuilder();
	    hql.append("delete from SoftProjectUserRelEntity pu where pu.type='"+type+"' and pu.projectId='"+projectId+"'");
	    Query query = getSession().createQuery(hql.toString());
	    query.executeUpdate();
	    for(String enname : selectedUserEnname)
	    {
	    	SoftProjectUserRelEntity pu = new SoftProjectUserRelEntity();
	    	pu.setPersonEnname(enname);
	    	pu.setProjectId(projectId);
	    	pu.setType(type);
	    	getSession().save(pu);
	    }
	 }

    /**
    * Title: getUserByProjectId
    * Description:获取项目底下的人员信息
    * @author    hjx 
    * @date       2013-1-30 
    * @param projectId
    * @return
    */
    @SuppressWarnings("unchecked")
	public List<Map<String,String>> getUserByProjectId(String projectId,String deptNo) {
		if(StrUtils.isEmpty(projectId));
		     new RuntimeException("项目Id不能为空");
		     StringBuilder hql = new StringBuilder();
		     hql.append(" select new map(u.enname as enname,u.cnname as cnname,pu.type as type) from  SysUserEntity u,SoftProjectUserRelEntity pu");
		     hql.append(" where u.enname = pu.personEnname");
		     hql.append(" and pu.projectId = '"+projectId+"'");
	    if(StrUtils.isNotEmpty(deptNo))
		     hql.append(" and u.deptNo = '"+deptNo+"'");
		     Query query = getSession().createQuery(hql.toString());
		     List<Map<String,String>> list = query.list();
		     List<String> ennameList = getEnnameList(list);
		     ennameList = removeDuplicate(ennameList);
		     List<Map<String,String>> tempList = new ArrayList<Map<String,String>>();
		     for(String enname : ennameList)
		     {
		    	 for(Map<String,String> map : list)
		    	 {
		    		 if(enname.equals(map.get("enname")))
		    		 {
		    			 tempList.add(map);
		    			 break;
		    		 }
		    	 }
		     }
		     return  tempList;      
	}
    /**
    * Title: getEnnameList
    * Description:获取所有项目中的人员的英文名
    * @author    hjx 
    * @date       2013-4-24 
    * @param list
    * @return
     */
    private List<String> getEnnameList(List<Map<String,String>> list)
    {
    	List<String> strList = new ArrayList<String>();
    	for(Map<String,String> map: list)
    	{
    		strList.add(map.get("enname"));
    	}
    	return strList;
    }
    /**
    * Title: removeDuplicate
    * Description:消除重复的人员
    * @author    hjx 
    * @date       2013-4-24 
    * @param list
    * @return
     */
    private List<String> removeDuplicate(List<String> list) {
        HashSet<String> h = new HashSet<String>(list);
        list.clear();
        list.addAll(h);
        return list;
    } 
	@SuppressWarnings("unchecked")
	public List<SoftProjectEntity> getByStatus() {
	        String hql="from SoftProjectEntity s where status = :status"; 
	        Query query=getSession().createQuery(hql);           //创建查询
	        query.setInteger("status",0);                        //进行绑定
	        return query.list();                                 //执行查询
	}

	/**
	* Title: getProjectListByEnname
	* Description:获取用户参与的所以项目
	* @author    hjx 
	* @date       2013-4-24 
	* @param enname
	*/
	public List<SoftProjectEntity> getProjectListByEnname(String enname) {
		if(StrUtils.isEmpty(enname));
		   new RuntimeException("英文 名称不能为空");
		   StringBuilder hql = new StringBuilder();
		   hql.append(" select distinct p" +
		   		      " from  SoftProjectEntity p,SoftProjectUserRelEntity pu");
		   hql.append(" where p.id = pu.projectId");
		   hql.append(" and pu.personEnname = '"+enname+"'");
		   hql.append(" or p.projectManager = '"+enname+"'");
	       Query query = getSession().createQuery(hql.toString());
	       @SuppressWarnings("unchecked")
		   List<SoftProjectEntity> list = query.list();
	       return  list;
	}


	public boolean getByName(String name) {
		String hql="from SoftProjectEntity s where name = :name"; 
        Query query=getSession().createQuery(hql);           //创建查询
        query.setString("name",name);                        //进行绑定
        return query.list().size()>0;                                 //执行查询
	}	
}
